import xlrd, xlwt
from common.big_screen_interface import BigScreenInterface
import json
import time

class WriteExcel():
    def __init__(self,sheet_name):
        # 创建一个workbook 设置编码  和worksheet
        self.wb = xlwt.Workbook(encoding='utf-8')
        self.ws = self.wb.add_sheet(sheet_name)

    def write_excel_head(self,head_col0,head_col1,head_col2,head_col3,head_col4,head_col5,head_col6,head_col7,head_col8,
                         head_col9,head_col10,head_col11,head_col12,head_col13, head_col14):
        self.ws.write(0, 0, head_col0)
        self.ws.write(0, 1, head_col1)
        self.ws.write(0, 2, head_col2)
        self.ws.write(0, 3, head_col3)
        self.ws.write(0, 4, head_col4)
        self.ws.write(0, 5, head_col5)
        self.ws.write(0, 6, head_col6)
        self.ws.write(0, 7, head_col7)
        self.ws.write(0, 8, head_col8)
        self.ws.write(0, 9, head_col9)
        self.ws.write(0, 10, head_col10)
        self.ws.write(0, 11, head_col11)
        self.ws.write(0, 12, head_col12)
        self.ws.write(0, 13, head_col13)
        self.ws.write(0, 14, head_col14)

    def write_excel_body(self, row, col, value):
        # 数据写入excel,参数对应 行, 列, 值
        self.ws.write(row, col, value)  # 不带样式的写入

    def save_excel(self,excel_name):
        current_time = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
        self.wb.save('/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_{}_{}.xls'.format(excel_name,current_time))



class ReadExcel(object):
    def __init__(self, data_path, sheetname, wenxin_step1_url,wenxin_step1_static):
        #定义一个属性接收文件路径
        self.data_path = data_path
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容
        self.table = self.data.sheet_by_name(self.sheetname)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows
        self.wenxin_step1_url = wenxin_step1_url
        self.wenxin_step1_static = wenxin_step1_static

    def readExcel(self):
        data = []
        for i in range(1, self.rowNum):
            # 获取单元格数据
            except_type = self.table.cell_value(i, 1)
            except_method = self.table.cell_value(i, 2)
            except_action = self.table.cell_value(i,3)

            content = self.table.cell_value(i, 4)
            contents = content.split('\n')
            # print(contents)
            for content in contents:
                # 调用文心接口,拿到返回的type和对应的method
                obj = BigScreenInterface(self.wenxin_step1_url)
                res, r_elapsed = obj.wenxin_step1(content)
                res_dic = json.loads(res)
                # 将content，返回type写入excel
                if self.sheetname != "数据分析类指令":
                    data.append({"content":content, "res":res_dic,"r_elapsed": r_elapsed,"except_type":except_type,"except_method":except_method,'except_action': except_action})
        return data

def generate_excel_body(data, obj,):
    # data值 [{"content":"放大地图"，"res":{……},"except_type":except_type,"except_method":except_method}, {},{}]
    for i in range(len(data)):
        # 1：第一列
        try:
            cmd = data[i]["content"]
        except Exception as e:
            cmd = "缺少指令"
        # 2：第二列
        try:
            cmdType = data[i]["res"]["data"]["type"]
        except Exception as e:
            cmdType = "无data.type字段"
        # 3:第三列
        if sheetname == "控屏类指令":
            try:
                method = data[i]["res"]["data"]["screenControl"]["method"]
            except Exception as e:
                method = '返回值中无screenControl.method'
        # 第四列
        if sheetname == "控屏类指令":
            try:
                action = data[i]["res"]["data"]["screenControl"]["action"]
            except Exception as e:
                action = '返回值中无screenControl.action'
        # 4：第五列
        expType = data[i]["except_type"]
        # 第六列
        expMethod = data[i]["except_method"]
        # 第七列
        expAction = data[i]["except_action"]
        # 第八列
        try:
            for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                if m['type'] == "请求意图参数":
                    reqChatTime = m["time"]
        except Exception as e:
            reqChatTime = "未获取到请求意图参数时间"
        # 第九列
        try:
            for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                if m['type'] == "意图识别返回":
                    respCtrlScreenTime = m["time"]
        except Exception as e:
            respCtrlScreenTime ="未获取到意图识别返回时间"
        # 第10列
        try:
            for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                if m['type'] == "意图识别返回":
                    respChatTime = m["time"]
        except Exception as e:
            respChatTime ="未获取到专业闲聊返回时间"
        # 第11列
        try:
            for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                if m['type'] == "开始响应":
                    respEndStartTime = m["time"]
        except Exception as e:
            respEndStartTime ="未获取到开始响应时间"
        # 第12列
        try:
            totalCost = data[i]["res"]["data"]["trace"]["spendTime"]
        except Exception as e:
            totalCost ="未获取到接口总耗时时间"

        # 第十三列：结论判断
        if sheetname != "数据分析类指令":
            if cmdType == expType and method == expMethod and action == expAction:
                obj.write_excel_body(i + 1, 12, "PASS")
            else:
                obj.write_excel_body(i + 1, 12, "FAIL")

        # 第十四列：个人计算的总耗时
        try:
            myCost = json.dumps(data[i]["r_elapsed"])
        except Exception as e:
            myCost = '未获取到接口总耗时时间'
        # 第十五列：备注
        if sheetname != "数据分析类指令":
            try:
                comment = str(data[i]["res"]["data"])
            except Exception as e:
                comment = str(data[i]["res"])

        obj.write_excel_body(i + 1, 0, cmd)                        # 第i+1行，第1列
        obj.write_excel_body(i + 1, 1, cmdType)                        # 第i+1行，第2列
        obj.write_excel_body(i + 1, 2, method)                        # 第i+1行，第3列
        obj.write_excel_body(i + 1, 3, action)                    # 第i+1行，第4列
        obj.write_excel_body(i + 1, 4, expType)                    # 第i+1行，第5列
        obj.write_excel_body(i + 1, 5, expMethod)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 6, expAction)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 7, reqChatTime)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 8, respCtrlScreenTime)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 9, respChatTime)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 10, respEndStartTime)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 11, totalCost)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 13, myCost)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 14, comment)                    # 第i+1行，第7列

    obj.save_excel(sheetname)


def main(data_path,sheetname,wenxin_step1_url,wenxin_step1_static):
    read_obj = ReadExcel(data_path,sheetname,wenxin_step1_url,wenxin_step1_static)
    data = read_obj.readExcel()

    write_obj = WriteExcel(sheetname)
    if sheetname == "控屏类指令":
        write_obj.write_excel_head("指令类型",
                                   "返回的type类型","返回的screenControl.method值","返回的screenControl.action值",
                                   "预期的type类型","预期的screenControl.method值","预期的screenControl.action值",
                                   "请求聊天内容时间",'判断控屏返回时间','专业闲聊返回时间','请求结束，开始响应时间','总耗时',
                                   "结论","个人计算的总耗时","备注")
    generate_excel_body(data, write_obj)


if __name__ == "__main__":

    data_path = "/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/resource/大屏数字人指令集-test.xlsx"
    sheetname_list = ["控屏类指令"]
    wenxin_step1_url = "https://219.135.214.196:9001/v2x/platform/wenxin/v2/chat"
    wenxin_step1_static = "https://219.135.214.196:9001/v2x/platform/wenxin/chat/statistic"

    for sheetname in sheetname_list:

        main(data_path,sheetname,wenxin_step1_url,wenxin_step1_static)








